package com.sojson.util.db.compare.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sojson.util.db.compare.bean.ColumnInfo;

import lombok.extern.slf4j.Slf4j;

/**
 * 数据库比较工具类
 * 
 * @author liu
 * @date 2022-11-21
 */
@Slf4j
public class DbCompareUtil {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    public static final String URL_143 = "jdbc:mysql://localhost:3306/information_schema";
    public static final String USER_NAME_143 = "root";
    public static final String PASSWORD_143 = "123456";
    public static final String TABLE_NAME_143 = "sojson";

    public static final String URL_144 = "jdbc:mysql://localhost:3306/information_schema";
    public static final String USER_NAME_144 = "root";
    public static final String PASSWORD_144 = "123456";
    public static final String TABLE_NAME_144 = "sojsonbak";

	//��һ��StringΪtableName �ڶ���StringΪ columnName
	public static HashMap<String, Map<String,ColumnInfo>> map_143 = new HashMap<>();
	public static HashMap<String, Map<String,ColumnInfo>> map_144 = new HashMap<>();

	public static Connection getConnection143() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL_143,
					USER_NAME_143, PASSWORD_143);
		} catch (Exception e) {
            log.error(e.getMessage(), e);
		}
		return conn;
	}

	public static Connection getConnection144() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL_144,
					USER_NAME_144, PASSWORD_144);
		} catch (Exception e) {
            log.error(e.getMessage(), e);
		}
		return conn;
	}

	public static void initData() {
		String tableSchema = TABLE_NAME_143;
		List<ColumnInfo> list_143 = getColumnInfoList(getConnection143(), tableSchema);
		initMap(list_143, map_143);
		tableSchema = TABLE_NAME_144;
		List<ColumnInfo> list_144 = getColumnInfoList(getConnection144(), tableSchema);
		initMap(list_144, map_144);
		System.out.println("143表总数："+map_143.size()+"---144上表总数："+map_144.size());
	}
	
	public static void initMap(List<ColumnInfo> list,HashMap<String, Map<String,ColumnInfo>> map){
		if(list==null || list.isEmpty()){
			System.out.println("没有表结构数据-----");
			return;
		}
		for(ColumnInfo info:list){
			String key = info.getTableName();
			Map<String,ColumnInfo> tmpMap = null;
			if(map.containsKey(key)){
				tmpMap = map.get(key);
			}else{
				tmpMap = new HashMap<>();
			}
			tmpMap.put(info.getColumnName(), info);
			map.put(key, tmpMap);
		}
	}
	
	public static List<ColumnInfo> getColumnInfoList(Connection conn,String tableSchema) {
		List<ColumnInfo> infoList = new ArrayList<>();
		Statement statement = null;
		ResultSet rs = null;
		String sql = "SELECT TABLE_NAME tableName,COLUMN_NAME columnName,DATA_TYPE columnType,"
				+ "CHARACTER_OCTET_LENGTH columnLen,COLUMN_KEY columnkey,EXTRA columnExtra,"
				+ "IS_NULLABLE isNullAble,COLUMN_DEFAULT columnDefault,COLUMN_COMMENT columnComment "
				+ "FROM COLUMNS WHERE TABLE_SCHEMA = '"+tableSchema+"'  ORDER BY TABLE_NAME,ORDINAL_POSITION";
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				ColumnInfo columnInfo = new ColumnInfo();
				columnInfo.setTableName(rs.getString("tableName")==null?"":rs.getString("tableName"));
				columnInfo.setColumnName(rs.getString("columnName")==null?"":rs.getString("columnName"));
				columnInfo.setColumnType(rs.getString("columnType")==null?"":rs.getString("columnType"));
				columnInfo.setColumnLen(rs.getString("columnLen")==null?"0":rs.getString("columnLen"));
				columnInfo.setColumnkey(rs.getString("columnkey")==null?"":rs.getString("columnkey"));
				columnInfo.setColumnExtra(rs.getString("columnExtra")==null?"":rs.getString("columnExtra"));
				columnInfo.setIsNullAble(rs.getString("isNullAble")==null?"":rs.getString("isNullAble"));
				columnInfo.setColumnDefault(rs.getString("columnDefault")==null?"":rs.getString("columnDefault"));
				columnInfo.setColumnComment(rs.getString("columnComment")==null?"":rs.getString("columnComment"));
				infoList.add(columnInfo);
			}
		} catch (Exception e) {
            log.error(e.getMessage(), e);
		} finally {
			close(rs, statement, conn);
		}
		return infoList;
	}

	public static void close(ResultSet rs, Statement sta, Connection conn) {
		try {
			if (rs != null) {
                rs.close();
            }
			if (sta != null) {
                sta.close();
            }
			if (conn != null) {
                conn.close();
            }
		} catch (Exception e) {
            log.error(e.getMessage(), e);
		}
	}

}